package cn.kgc.tangcco.tcbd1011.books.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.kgc.tangcco.tcbd1011.books.dao.BooksDao;
import cn.kgc.tangcco.tcbd1011.books.entity.Books;
import cn.kgc.tangcco.tcbd1011.books.vo.BooksVo;
import cn.kgc.tangcco.tcbd1011.util.JDBCUtil;


public class BooksDaoImpl implements BooksDao {
	private QueryRunner run;
	public BooksDaoImpl() {
		this.run=new QueryRunner(JDBCUtil.getDataSource());
	}
	@Override
	public int addBooks(Books books) {
		try {
			return this.run.update("insert into books values(default,?,?,?,?,now())",books.getTypid(),books.getBookname(),books.getAuthor(),books.getPublish());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int updateBooks(Books books) {
		try {
			return this.run.update("update books set typid=?,bookname=?,author=? publish=? where bookid=?"
					,books.getTypid(),books.getBookname(),books.getAuthor(),books.getPublish(),books.getBookid());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int deleteBooks(int pk) {
		try {
			return this.run.update("delete from books where bookid=?",pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public List<BooksVo> queryAllBooks(BooksVo booksvo) {
		String sql="SELECT t.typename ,b.* FROM booktype t,books b WHERE t.typeid=b.typeid";
		
		StringBuffer  sb=new StringBuffer();
		List<Object>  list=new ArrayList<>();
		sb.append(sql);
		if(booksvo.getBookname()!=null  && !booksvo.getBookname().isEmpty()) {
			sb.append(" and b.bookname like ?");
			list.add("%"+booksvo.getBookname()+"%");
		}
		if(booksvo.getTypeid()!=null  && booksvo.getTypeid()>0) {
			sb.append(" and b.typeid=?");
			list.add(booksvo.getTypeid());
		}
		sb.append(" order by bookid desc");
		sql=sb.toString();
		
		try {
			return this.run.query(sql, new BeanListHandler<>(BooksVo.class),list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public BooksVo detailBooks(int pk) {
		try {
			return this.run.query("select *from books where bookid=?", new BeanHandler<>(BooksVo.class),pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public int queryCountBooks() {
		try {
			return this.run.query("select count(*)from books", new ScalarHandler<>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public List<Books> queryTypeToBooks() {
		try {
			return this.run.query("select * from books order by bookid desc", new BeanListHandler<>(Books.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
}
